/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.thienloc.omap.jdbc.dao;

import com.thienloc.omap.jdbc.entity.TblSmsBroadcasting;
import com.thienloc.omap.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.slf4j.LoggerFactory;

/**
 *
 * @author GiangLT
 */
public class TblSmsBroadcastingDao extends BaseDao {

    public int add(TblSmsBroadcasting item) throws SQLException {
        int result = 0;
        Connection con = DBUtil.getConnection(TLSMS_JNDI);
        if (con == null) {
            return result;
        }
        if (item == null) {
            return 0;
        }
        PreparedStatement ps = null;
        try {
            String cmd = "INSERT INTO TBL_SMS_BROADCASTING(sc, msisdn, sms, provider, result, sent_time)"
                    + " VALUES(?,?,?,?,?,?)";
            ps = con.prepareStatement(cmd);
            ps.setString(1, item.getSc());
            ps.setString(2, item.getMsisdn());
            ps.setString(3, item.getSms());
            ps.setString(4, item.getProvider());
            ps.setString(5, item.getResult());
            ps.setString(6, item.getSentTime());
            result = ps.executeUpdate();
            DbAccess.commitTransaction(con);
        } finally {
            DbAccess.closePreparedStatement(ps);
            DbAccess.closeConnection(con);
        }
        return result;
    }

    public int[] add(List<TblSmsBroadcasting> items) throws SQLException {
        Connection con = DBUtil.getConnection(TLSMS_JNDI);
        if (con == null) {
            return new int[0];
        }
        try {
            Object[][] params = new Object[items.size()][];
            int i = 0;
            for (TblSmsBroadcasting item : items) {
                Object[] row = new Object[]{
                    item.getSc(),
                    item.getMsisdn(),
                    item.getSms(),
                    item.getProvider(),
                    item.getResult(),
                    item.getSentTime()
                };
                params[i++] = row;
            }

            String cmd = "INSERT INTO TBL_SMS_BROADCASTING(sc, msisdn, sms, provider, result, sent_time)"
                    + " VALUES(?,?,?,?,?,?)";
            return new QueryRunner().batch(con, cmd, params);
        } finally {
            DbUtils.commitAndCloseQuietly(con);
        }
    }

    public List<TblSmsBroadcasting> findByMsisdn(String msisdn, Date startDate, Date endDate) throws SQLException {
        Connection con = DBUtil.getConnection(TLSMS_JNDI);
        if (con == null) {
            return new ArrayList<TblSmsBroadcasting>(0);
        }

        String sql = "SELECT * FROM tbl_sms_broadcasting WHERE sent_time >= ? and sent_time < ? AND msisdn = ?";
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = con.prepareStatement(sql);
            ps.setString(1, df.format(startDate));
            ps.setString(2, df.format(endDate));
            ps.setString(3, msisdn);
            rs = ps.executeQuery();

            List<TblSmsBroadcasting> result = new ArrayList<TblSmsBroadcasting>();
            while (rs.next()) {
                int i = 1;
                TblSmsBroadcasting item = new TblSmsBroadcasting();
                item.setSc(rs.getString(i++));
                item.setMsisdn(rs.getString(i++));
                item.setSms(rs.getString(i++));
                item.setProvider(rs.getString(i++));
                item.setResult(rs.getString(i++));
                item.setSentTime(rs.getString(i++));
                result.add(item);
            }
            return result;
        } finally {
            DbAccess.closeResultSet(rs);
            DbAccess.closePreparedStatement(ps);
            DbAccess.closeConnection(con);
        }
    }

    public Map<String, Integer> find(String month, String year, List<String> providers) throws SQLException {
        Map<String, Integer> result = new HashMap<String, Integer>();
        Connection con = DBUtil.getConnection(TLSMS_JNDI);

        if (con == null) {
            return result;
        }

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(*),sms FROM tbl_sms_broadcasting PARTITION (P");
        sql.append(year);
        sql.append(month);
        sql.append(") WHERE provider IN ( ");
        for (String provider : providers) {
            sql.append("'").append(provider).append("'").append(",");
        }
        sql.deleteCharAt(sql.lastIndexOf(","));
        sql.append(") GROUP BY sms");

        LoggerFactory
                .getLogger(TblSmsBroadcastingDao.class
                ).debug(sql.toString());
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = con.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                Integer count = rs.getInt(1);
                String sms = rs.getString(2);
                result.put(sms, count);
            }
        } finally {
            DbAccess.closeResultSet(rs);
            DbAccess.closePreparedStatement(ps);
            DbAccess.closeConnection(con);
        }

        return result;
    }

    public Map<String, Integer> find(String day, String month, String year, List<String> providers) throws SQLException {
        Map<String, Integer> result = new HashMap<String, Integer>();
        Connection con = DBUtil.getConnection(TLSMS_JNDI);

        if (con == null) {
            return result;
        }

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(*),sms FROM tbl_sms_broadcasting PARTITION (P");
        sql.append(year);
        sql.append(month);
        sql.append(") WHERE SUBSTR(sent_time, 0, 10) = '");
        sql.append(year).append("-").append(month).append("-").append(day);
        sql.append("' AND provider IN ( ");
        for (String provider : providers) {
            sql.append("'").append(provider).append("'").append(",");
        }
        sql.deleteCharAt(sql.lastIndexOf(","));
        sql.append(" ) GROUP BY sms");

        LoggerFactory
                .getLogger(TblSmsBroadcastingDao.class
                ).debug(sql.toString());
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = con.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                Integer count = rs.getInt(1);
                String sms = rs.getString(2);
                result.put(sms, count);
            }
        } finally {
            DbAccess.closeResultSet(rs);
            DbAccess.closePreparedStatement(ps);
            DbAccess.closeConnection(con);
        }

        return result;
    }

    public Integer countProduction(String sms, List<String> providers, String month, String year, Integer mdnSuffixStartRange, Integer mdnSuffixEndRange) throws SQLException {
        Integer result = 0;
        Connection con = DBUtil.getConnection(TLSMS_JNDI);

        if (con == null) {
            return result;
        }

        int suffixLength = mdnSuffixEndRange.toString().length();
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT COUNT(*) FROM tbl_sms_broadcasting PARTITION (P");
        sql.append(year);
        sql.append(month);
        sql.append(") ");

        sql.append(" WHERE ");
        sql.append(" provider IN ( ");
        for (String provider : providers) {
            sql.append("'");
            sql.append(provider);
            sql.append("'");
            sql.append(",");
        }
        sql.deleteCharAt(sql.lastIndexOf(","));
        sql.append(" ) ");

        sql.append(" AND ");
        sql.append(" sms = ? ");

        sql.append(" AND ");
        sql.append(" TO_NUMBER(SUBSTR(msisdn, LENGTH(msisdn)-");
        sql.append(suffixLength - 1);
        sql.append(", ");
        sql.append(suffixLength);
        sql.append(")) >= ");
        sql.append(mdnSuffixStartRange);

        sql.append(" AND ");
        sql.append(" TO_NUMBER(SUBSTR(msisdn, LENGTH(msisdn)-");
        sql.append(suffixLength - 1);
        sql.append(", ");
        sql.append(suffixLength);
        sql.append(")) <= ");
        sql.append(mdnSuffixEndRange);

        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = con.prepareStatement(sql.toString());
            ps.setString(1, sms);
            rs = ps.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
            }
        } finally {
            DbAccess.closeResultSet(rs);
            DbAccess.closePreparedStatement(ps);
            DbAccess.closeConnection(con);
        }
        return result;
    }

    public Integer countProduction(List<String> smses, List<String> providers, String day, String month, String year, Integer mdnSuffixStartRange, Integer mdnSuffixEndRange) throws SQLException {
        Integer result = 0;
        Connection con = DBUtil.getConnection(TLSMS_JNDI);

        if (con == null) {
            return result;
        }

        int suffixLength = mdnSuffixEndRange.toString().length();
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT COUNT(*) FROM tbl_sms_broadcasting PARTITION (P");
        sql.append(year);
        sql.append(month);
        sql.append(") ");

        sql.append(" WHERE ");
        sql.append(" provider IN ( ");
        for (String provider : providers) {
            sql.append("'");
            sql.append(provider);
            sql.append("'");
            sql.append(",");
        }
        sql.deleteCharAt(sql.lastIndexOf(","));
        sql.append(" ) ");

        sql.append(" AND ");
        sql.append(" SUBSTR(sent_time, 0, 10) = '");
        sql.append(year);
        sql.append("-");
        sql.append(month);
        sql.append("-");
        sql.append(day);
        sql.append("' ");

        sql.append(" AND ");
        sql.append(" sms IN ( ");
        for (int i = 0; i < smses.size(); i++) {
            sql.append("?, ");
        }
        sql.deleteCharAt(sql.lastIndexOf(","));
        sql.append(") ");

        sql.append(" AND ");
        sql.append(" TO_NUMBER(SUBSTR(msisdn, LENGTH(msisdn)-");
        sql.append(suffixLength - 1);
        sql.append(", ");
        sql.append(suffixLength);
        sql.append(")) >= ");
        sql.append(mdnSuffixStartRange);

        sql.append(" AND ");
        sql.append(" TO_NUMBER(SUBSTR(msisdn, LENGTH(msisdn)-");
        sql.append(suffixLength - 1);
        sql.append(", ");
        sql.append(suffixLength);
        sql.append(")) <= ");
        sql.append(mdnSuffixEndRange);

        LoggerFactory
                .getLogger(TblSmsBroadcastingDao.class
                ).trace(sql.toString());
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = con.prepareStatement(sql.toString());
            int paramIndex = 1;
            for (String sms : smses) {
                ps.setString(paramIndex++, sms);
            }
            rs = ps.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
            }
        } finally {
            DbAccess.closeResultSet(rs);
            DbAccess.closePreparedStatement(ps);
            DbAccess.closeConnection(con);
        }
        return result;
    }

    public Integer countProduction(List<String> smses, List<String> providers, String month, String year, Integer mdnSuffixStartRange, Integer mdnSuffixEndRange) throws SQLException {
        Integer result = 0;
        Connection con = DBUtil.getConnection(TLSMS_JNDI);

        if (con == null) {
            return result;
        }

        int suffixLength = mdnSuffixEndRange.toString().length();
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT COUNT(*) FROM tbl_sms_broadcasting PARTITION (P");
        sql.append(year);
        sql.append(month);
        sql.append(") ");

        sql.append(" WHERE ");
        sql.append(" provider IN ( ");
        for (String provider : providers) {
            sql.append("'");
            sql.append(provider);
            sql.append("'");
            sql.append(",");
        }
        sql.deleteCharAt(sql.lastIndexOf(","));
        sql.append(" ) ");

        sql.append(" AND ");
        sql.append(" sms IN ( ");
        for (int i = 0; i < smses.size(); i++) {
            sql.append("?,");
        }
        sql.deleteCharAt(sql.lastIndexOf(","));
        sql.append(" ) ");

        sql.append(" AND ");
        sql.append(" TO_NUMBER(SUBSTR(msisdn, LENGTH(msisdn)-");
        sql.append(suffixLength - 1);
        sql.append(", ");
        sql.append(suffixLength).append(")) >= ");
        sql.append(mdnSuffixStartRange);

        sql.append(" AND ");
        sql.append(" TO_NUMBER(SUBSTR(msisdn, LENGTH(msisdn)-");
        sql.append(suffixLength - 1);
        sql.append(", ");
        sql.append(suffixLength).append(")) <= ");
        sql.append(mdnSuffixEndRange);

        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = con.prepareStatement(sql.toString());
            int paramIndex = 1;
            for (String sms : smses) {
                ps.setString(paramIndex++, sms);
            }
            rs = ps.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
            }
        } finally {
            DbAccess.closeResultSet(rs);
            DbAccess.closePreparedStatement(ps);
            DbAccess.closeConnection(con);
        }
        return result;
    }
}
